package com.miniserver.tql.database.impl;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Types;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import net.sf.json.util.JSONTokener;

import org.apache.commons.lang.StringUtils;

import com.miniserver.tql.database.DatabaseEngine;
import com.miniserver.tql.database.EngineContext;
import com.miniserver.tql.database.Query;
import com.miniserver.tql.database.model.TAttr;
import com.miniserver.tql.database.model.TNode;
import com.miniserver.util.StringFormatUtil;

public class QueryImpl implements Query {
	public QueryImpl(DatabaseEngine engine, EngineContext context, ResultSet rs, TNode tQuery, long recordCount)
			throws Exception {
		this.rs = rs;
		this.engine = engine;
		this.context = context;
		this.totalCount = recordCount;
		this.init(tQuery);
	}

	private static class Foreach {
		public String name;
		public TNode node;
		public String value;
	};

	private static class RecList {
		public String name;
		public String format;
		//
		public StringBuilder value = new StringBuilder();
		public ArrayList<String> fields;
	};

	private long totalCount = 0;
	private long recordCount = 0;
	private ResultSet rs = null;
	private String tableName = "";
	private EngineContext context;
	private DatabaseEngine engine;
	private boolean isRowToCol = false;
	private String rowToColNameField = null;
	private String rowToColValueField = null;
	//
	private boolean onlyOnceReady = false;
	private ArrayList<TNode> onlyOnceNodes = new ArrayList<TNode>();
	private ArrayList<RecList> listNodes = new ArrayList<RecList>();
	private ArrayList<Foreach> foreachNodes = new ArrayList<Foreach>();
	private HashMap<String, Integer> columns = new HashMap<String, Integer>();

	private void init(TNode tQuery) throws Exception {
		// 获取TableName
		TAttr tableAlias = tQuery.getAttribute("tableAlias");
		if (tableAlias != null) {
			this.tableName = tableAlias.getValue();
		} else {
			this.tableName = engine.getTableName(context, rs);
		}
		// 缓存列名和列索引的对应关系
		ResultSetMetaData rsmd = rs.getMetaData();
		for (int i = 1; i != rsmd.getColumnCount() + 1; i++) {
			this.columns.put(rsmd.getColumnLabel(i).toLowerCase(), i);
		}
		// 获取所有子节点
		TNode[] childNodes = tQuery.getChildren();
		for (TNode tChild : childNodes) {
			if (tChild == null) {
				continue;
			} else if (tChild.getNodeName().equals("foreach")) {
				this.createForeach(tChild);
			} else {
				this.createVar(tChild);
			}
		}
		// 为默认返回值添加变量值
		TAttr tResult = tQuery.getAttribute("result");
		if (tResult == null) {
			RecList result = new RecList();
			result.fields = null;
			result.format = context.get("?format");
			result.name = "@return";
			this.listNodes.add(result);
		}
		// 获取所有列名
		for (RecList tVar : this.listNodes) {
			if (tVar.fields != null) {
				continue;
			}
			//
			tVar.fields = new ArrayList<String>();
			for (String s : this.columns.keySet()) {
				tVar.fields.add(s);
			}
			//
			for (Foreach tForeach : this.foreachNodes) {
				if (tForeach.name != null && !tForeach.name.equals("")) {
					tVar.fields.add(tForeach.name);
				}
			}
		}
		// 结果行列互换
		TAttr tRowToCol = tQuery.getAttribute("rowtocol");
		if (tRowToCol != null && tRowToCol.getValue().compareToIgnoreCase("true") == 0) {
			TAttr tRowToColName = tQuery.getAttribute("rowtocolname");
			TAttr tRowToColValue = tQuery.getAttribute("rowtocolvalue");
			if (tRowToColName != null && StringUtils.isNotBlank(tRowToColName.getValue()) && tRowToColValue != null
					&& StringUtils.isNotBlank(tRowToColValue.getValue())) {
				this.isRowToCol = true;
				rowToColNameField = tRowToColName.getValue();
				rowToColValueField = tRowToColValue.getValue();
			}
		}
	}

	private void createForeach(TNode tChild) {
		Foreach child = new Foreach();
		TAttr tName = tChild.getAttribute("output");
		if (tName == null) {
			child.name = "";
		} else {
			child.name = tName.getValue();
		}
		child.node = tChild;
		TAttr tSumColAttr = tChild.getAttribute("sumCols");
		if (tSumColAttr != null && StringUtils.isNotEmpty(tSumColAttr.getValue())) {
			context.put("@_sumCols_", tSumColAttr.getValue());
		}
		//
		foreachNodes.add(child);
	}

	private Foreach getForeachNode(String name) {
		for (int i = 0; i < this.foreachNodes.size(); i++) {
			Foreach tResult = this.foreachNodes.get(i);
			if (tResult.name.equals(name)) {
				return tResult;
			}
		}
		//
		return null;
	}

	private void createVar(TNode tChild) {
		if (!tChild.getNodeName().equals("set")) {
			this.onlyOnceNodes.add(tChild);
			return;
		}
		//
		TAttr tType = tChild.getAttribute("type");
		if (tType == null || !tType.getValue().equals("list")) {
			this.onlyOnceNodes.add(tChild);
			return;
		}
		//
		RecList child = new RecList();
		TAttr tName = tChild.getAttribute("name");
		if (tName == null) {
			return;
		}
		//
		child.name = tName.getValue();
		TAttr tFormat = tChild.getAttribute("format");
		if (tFormat == null) {
			child.format = context.get("?format");
		} else {
			child.format = tFormat.getValue();
		}
		//
		TAttr tCol = tChild.getAttribute("column");
		String colNames = (tCol == null) ? "*" : tCol.getValue();
		if (colNames == null || colNames.equals("") || colNames.equals("*")) {
			child.fields = null;
		} else {
			String[] cols = tCol.getValue().split(";");
			child.fields = new ArrayList<String>();
			for (String s : cols) {
				child.fields.add(s.trim());
			}
		}
		//
		this.listNodes.add(child);
	}

	private void outputRecord(RecList tList) throws Exception {
		if (tList.format.equals("json")) {
			StringBuilder rv = tList.value;
			boolean first = true;
			if (rv.length() > 0) {
				rv.append(",{");
			} else {
				rv.append("{");
			}
			//
			for (String s : tList.fields) {
				String v = this.field(s, tList.format, "''");
				if (first) {
					rv.append('\"').append(s).append("\":").append(v);
					first = false;
				} else {
					rv.append(",\"").append(s).append("\":").append(v);
				}
			}
			if (this.context.contains("@_sumCols_")) {
				for (String s : this.context.get("@_sumCols_").split(",")) {
					String v;
					String key = String.format("@_sum_%s", s);
					v = this.sumField(key);
					if (StringUtils.isNotBlank(v)) {
						rv.append(",\"").append(key).append("\":").append(v);
						this.context.put(key, "0");
					}
				}
			}
			rv.append("}");
		} else if (tList.format.equals("xml")) {
			StringBuilder rv = tList.value;
			rv.append('<').append(this.tableName).append('>');
			for (String s : tList.fields) {
				String v = this.field(s, tList.format, "");
				rv.append('<').append(s).append('>').append(v).append("</").append(s).append('>');
			}
			rv.append("</").append(this.tableName).append('>');
		} else if (tList.format.equals("sql")) {
			boolean first = true;
			StringBuilder rv = tList.value;
			for (String s : tList.fields) {
				String v = this.field(s, tList.format, "''");
				if (first) {
					rv.append(v);
					first = false;
				} else {
					rv.append(',').append(v);
				}
			}
		} else if (tList.format.equals("plain")) {
			boolean first = true;
			StringBuilder rv = tList.value;
			if (rv.length() > 0) {
				first = false;
			}
			for (String s : tList.fields) {
				String v = this.field(s, tList.format, "''");
				if (StringUtils.isBlank(v))
					continue;
				if (first) {
					rv.append(v);
					first = false;
				} else {
					rv.append(',').append(v);
				}
			}
		} else {
			throw new Exception("不支持的ResultSet输出类型");
		}
	}

	public void run() throws Exception {
		while (this.next()) {
			this.recordCount++;
			// ;
		}

		//
		for (RecList tVar : this.listNodes) {
			// 添加记录总数和success标记
			if (this.totalCount > 0) {
				if (tVar.format.equals("json")) {
					// 处理行列转换
					if (this.isRowToCol) {
						convertRowToColModel(tVar.value);
						this.totalCount = 1;
					}
					tVar.value.insert(0, "{\"data\":[");
					tVar.value.append("],\"count\":").append(this.totalCount);
					tVar.value.append(",\"success\":true}");
				} else if (tVar.format.equals("xml")) {
					tVar.value.append("<count>").append(this.totalCount).append("</count>");
				}
			} else {
				if (tVar.format.equals("json")) {
					tVar.value.insert(0, "{\"data\":[");
					tVar.value.append("],\"count\":").append(this.recordCount);
					tVar.value.append(",\"success\":true}");
				} else if (tVar.format.equals("xml")) {
					tVar.value.append("<count>").append(this.recordCount).append("</count>");
				}
			}
			System.out.println(tVar.name + ": " + tVar.value.toString());
			// 输出变量值
			context.put(tVar.name, tVar.value.toString());
		}
	}

	private void convertRowToColModel(StringBuilder valueBuffer) {
		valueBuffer.insert(0, "{\"data\":[");
		valueBuffer.append("]}");
		JSONTokener jsonParser = new JSONTokener(valueBuffer.toString());
		JSONObject jsonObject = (JSONObject) jsonParser.nextValue();
		JSONArray jsonObjs = jsonObject.getJSONArray("data");
		valueBuffer.setLength(0);
		valueBuffer.append("{");
		for (int i = 0; i < jsonObjs.size(); i++) {
			JSONObject tempObj = ((JSONObject) jsonObjs.opt(i));
			if (i > 0) {
				valueBuffer.append(",");
			}
			valueBuffer.append("\"").append(tempObj.get(this.rowToColNameField));
			valueBuffer.append("\":\"").append(tempObj.get(this.rowToColValueField)).append("\"");
		}
		valueBuffer.append("}");
	}

	private boolean next() throws Exception {
		boolean hasNext = this.rs.next();
		if (!hasNext) {
			return false;
		}
		//
		for (Foreach tForeach : this.foreachNodes) {
			engine.foreach(context, tForeach.node, this);
			TAttr tResult = tForeach.node.getAttribute("result");
			String resultVarName = (tResult == null) ? "@return" : tResult.getValue();
			tForeach.value = context.get(resultVarName);
		}
		//
		for (RecList tVar : this.listNodes) {
			this.outputRecord(tVar);
		}
		//
		if (this.onlyOnceReady) {
			return hasNext;
		}
		//
		for (TNode tChild : this.onlyOnceNodes) {
			if (tChild.getNodeName().equals("set")) {
				this.set(this.context, tChild);
			} else {
				this.engine.invoke(this.context, tChild);
			}
		}
		//
		this.onlyOnceReady = true;
		//
		return hasNext;
	}

	private String getColumnValue(int index, String format, String defaultValue) throws Exception {
		ResultSetMetaData rsmd = rs.getMetaData();
		if (index > rsmd.getColumnCount()) {
			return defaultValue;
		}
		//
		int type = rsmd.getColumnType(index);
		switch (type) {
		case Types.INTEGER:
			return String.valueOf(rs.getInt(index));
		case Types.DECIMAL:
			return String.valueOf(rs.getDouble(index));
		case Types.DOUBLE:
			return String.valueOf(rs.getDouble(index));
		case Types.NUMERIC:
			return String.valueOf(rs.getDouble(index));
		case Types.SMALLINT:
			return String.valueOf(rs.getShort(index));
		case Types.BOOLEAN:
			return String.valueOf(rs.getBoolean(index));
		case Types.BIT:
			String vString = rs.getString(index);
			String returnString = "null";
			if (vString != null && vString.equals("f")) {
				returnString = "false";
			} else if (vString != null && vString.equals("t")) {
				returnString = "true";
			}
			return returnString;
		default:
			break;
		}
		//
		if (format == null) {
			format = "plain";
		}
		//
		if (format.equals("json")) {
			switch (type) {
			case Types.BIGINT:
				// modify by ddl bigint类型的json在html中超过了javascript的精度极限，应该使用字符串输出
				return "\"" + String.valueOf(rs.getLong(index)) + "\"";
			case Types.VARCHAR:
			case Types.CHAR:
			case Types.LONGVARCHAR:
				return "\"" + StringFormatUtil.convertToJsonString(StringUtils.trim(rs.getString(index))) + "\"";
			case Types.TIMESTAMP:
				Date date = rs.getTimestamp(index);
				if (date != null) {
					DateFormat df = new SimpleDateFormat("\"yyyy-MM-dd HH:mm:ss\"");
					return df.format(date);
				} else {
					return "\"1970-01-01 00:00:00\"";
				}
			default:
				return "\"" + StringFormatUtil.convertToJsonString(StringUtils.trim((String) rs.getObject(index)))
						+ "\"";
			}
		} else if (format.equals("sql")) {
			switch (type) {
			case Types.BIGINT:
				return String.valueOf(rs.getLong(index));
			case Types.VARCHAR:
				return "'" + StringFormatUtil.convertToSqlString(StringUtils.trim(rs.getString(index))) + "'";
			case Types.TIMESTAMP:
				Date date = rs.getTimestamp(index);
				if (date != null) {
					// DateFormat df = new
					// SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					// return df.format(date);
					return Long.toString(date.getTime());
				} else {
					return "\"1970-01-01 00:00:00\"";
				}
			default:
				return "'" + StringFormatUtil.convertToSqlString(StringUtils.trim((String) rs.getObject(index))) + "'";
			}
		} else if (format.equals("xml")) {
			switch (type) {
			case Types.BIGINT:
				return String.valueOf(rs.getLong(index));
			case Types.VARCHAR:
				return StringFormatUtil.formatString2XML(StringUtils.trim(rs.getString(index)));
			case Types.TIMESTAMP:
				Date date = rs.getTimestamp(index);
				if (date != null) {
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					return df.format(date);
				} else {
					return "\"1970-01-01 00:00:00\"";
				}
			default:
				return StringUtils.trim((String) rs.getObject(index));
			}
		} else {
			switch (type) {
			case Types.BIGINT:
				return String.valueOf(rs.getLong(index));
			case Types.VARCHAR:
				return StringUtils.trim(rs.getString(index));
			case Types.TIMESTAMP:
				Date date = rs.getTimestamp(index);
				if (date != null) {
					DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
					return df.format(date);
				} else {
					return "\"1970-01-01 00:00:00\"";
				}
			default:
				return StringUtils.trim((String) rs.getObject(index));
			}
		}
	}

	private void sumValues(int index, String cacheName) throws Exception {
		ResultSetMetaData rsmd = rs.getMetaData();
		if (index < 0 && index > rsmd.getColumnCount()) {
			return;
		}
		//
		int type = rsmd.getColumnType(index);
		switch (type) {
		case Types.INTEGER: {
			int sum = Integer.valueOf(this.context.get(cacheName, "0"));
			this.context.put(cacheName, String.valueOf(sum + rs.getInt(index)));
			break;
		}
		case Types.DECIMAL:
		case Types.DOUBLE:
		case Types.NUMERIC: {
			Double sum = Double.valueOf(this.context.get(cacheName, "0.0"));
			this.context.put(cacheName, String.valueOf(sum + rs.getDouble(index)));
			break;
		}
		case Types.SMALLINT: {
			int sum = Integer.valueOf(this.context.get(cacheName, "0"));
			this.context.put(cacheName, String.valueOf(sum + rs.getShort(index)));
			break;
		}
		default:
			break;
		}

	}

	public String field(String fieldName, String format, String defaultValue) {
		Foreach tForeach = this.getForeachNode(fieldName);
		if (tForeach != null) {
			return tForeach.value;
		}
		//
		if (this.columns.containsKey(fieldName)) {
			try {
				int index = this.columns.get(fieldName);
				return this.getColumnValue(index, format, defaultValue);
			} catch (Exception e) {
				e.printStackTrace();
				return defaultValue;
			}
		} else {
			return defaultValue;
		}
	}

	public String sumField(String fieldName) {
		if (this.foreachNodes.size() > 0) {
			return this.context.get(fieldName, "0");
		}
		if (this.columns.containsKey(fieldName.replace("@_sum_", ""))) {
			try {
				int index = this.columns.get(fieldName.replace("@_sum_", ""));
				this.sumValues(index, fieldName);
				return "";
			} catch (Exception e) {
			}
		}
		return "";
	}

	@Override
	public void set(EngineContext context, TNode tVar) {
		TAttr tName = tVar.getAttribute("name");
		if (tName == null) {
			return;
		}
		//
		TAttr tField = tVar.getAttribute("column");
		if (tField == null) {
			return;
		}
		//
		TAttr tFormat = tVar.getAttribute("format");
		String format = (tFormat == null) ? "plain" : tFormat.getValue();
		String fieldValue = this.field(tField.getValue(), format, "");
		System.out.println("set:" + tName.getValue() + "=" + fieldValue);
		context.put(tName.getValue(), fieldValue);
	}
}
